﻿
CREATE PROCEDURE [dbo].[InchideAn]  
@pAn as int,
@codUnit as smallint AS
BEGIN

---creaza tabelele corespunzatoare
EXEC CreazaArhiva @An = @pAn
DECLARE @strAn varchar(4)
DECLARE @Comanda varchar(5000)
DECLARE @strCodUnitate varchar(40)

SET @strAn=CAST(@pAn AS varchar(4))
SET @strCodUnitate =  CAST(@codUnit AS varchar(20))

BEGIN TRAN 
PRINT('ArticoleAliniate')
--ArticoleAliniate---------------------------------------
DELETE FROM [arhArticoleAliniate] WHERE [CodUnitate]=@codUnit AND An=@pAn
IF @@ERROR != 0 
	GOTO EROARE

INSERT INTO [arhArticoleAliniate] SELECT [ArtAl]
      ,[Denumire]
      ,[AAParinte]
      ,[Terminal]
      ,[Titlu]
      ,[Categorii]
      ,[TermDenumire]
      ,[StartDenumire]
      ,[Anexa6]
      ,[CodUnitate]
      ,@pAn
	  ,[ExecutieCheltuieli]
	   FROM [ArticoleAliniate] WHERE [CodUnitate]=@codUnit
IF @@ERROR != 0 
	GOTO EROARE

PRINT('Bilant')
--Bilant---------------------------------------
DELETE FROM [arhBilant] WHERE [CodUnitate]=@codUnit AND An=@pAn
IF @@ERROR != 0 
	GOTO EROARE

INSERT INTO [arhBilant] SELECT *,@pAn FROM [Bilant] WHERE [CodUnitate]=@CodUnit
IF @@ERROR != 0 
	GOTO EROARE

PRINT('Sterge rulaje')
DELETE FROM [arhRulaje] WHERE [CodUnitate]=@CodUnit AND An=@pAn
IF @@ERROR != 0 
	GOTO EROARE
	
PRINT('Sterge rulajeDoc')
DELETE FROM [arhRulajeDoc] WHERE [CodUnitate]=@CodUnit AND An=@pAn
IF @@ERROR != 0 
	GOTO EROARE

PRINT('Conturi')
--Conturi---------------------------------------
DELETE FROM [arhConturi] WHERE [CodUnitate]=@CodUnit AND An=@pAn
IF @@ERROR != 0 
	GOTO EROARE

INSERT INTO [arhConturi]([Cont],[CodUnitate],[Denumire],[Tip],[Terminal],[ContParinte],[EsteAnalitic],[DebitInitial],[CreditInitial],[DebitPrecedent],[CreditPrecedent],[Categorie],[RulajDebit],[RulajCredit],[Sold],[An]) 
	SELECT [Cont],[CodUnitate],[Denumire],[Tip],[Terminal],[ContParinte],[EsteAnalitic],[DebitInitial],[CreditInitial],[DebitPrecedent],[CreditPrecedent],[Categorie],[RulajDebit],[RulajCredit],[Sold],@pAn FROM [Conturi] WHERE [CodUnitate]=@CodUnit
IF @@ERROR != 0 
	GOTO EROARE

PRINT('Conturi Valuta')
--ConturiValuta---------------------------------------
DELETE FROM [arhConturiValuta] WHERE [CodUnitate]=@CodUnit AND An=@pAn
IF @@ERROR != 0 
	GOTO EROARE

INSERT INTO [arhConturiValuta] SELECT *,@pAn FROM [ConturiValuta] WHERE [CodUnitate]=@CodUnit
IF @@ERROR != 0 
	GOTO EROARE

PRINT('calculeaza rulajul total in credit precedent int abela conturi')
--calculeaza rulajul total in credit precedent int abela conturi

UPDATE [Conturi] SET [DebitPrecedent] = 0, [CreditPrecedent]=0, [RulajDebit]=0, [RulajCredit]=0 WHERE [CodUnitate]=@codUnit
IF @@ERROR != 0 
	GOTO EROARE

--UPDATE [Conturi] SET 
--RulajDebit = DebitInitial + (SELECT coalesce(SUM(r.ValoareLei),0) FROM Rulaje r JOIN RulajeDoc rd ON r.IDDocument=rd.IDDocument WHERE
 --year(rd.DataDoc)=@pAn and dbo.IsContInclus(Cont,r.ContDebit,Terminal)=1 AND r.[CodUnitate]=@CodUnit)
--WHERE cont<>'N/A' AND [CodUnitate]=@codUnit

UPDATE [Conturi] SET 
RulajDebit = a.RulajCurent FROM
(
 SELECT coalesce(SUM(r.ValoareLei),0) RulajCurent,r.ContDebit  FROM Rulaje r JOIN RulajeDoc rd ON r.IDDocument=rd.IDDocument WHERE
 year(rd.DataDoc)=@pAn  AND r.[CodUnitate]=@CodUnit
 group by r.ContDebit
) a
WHERE cont<>'N/A' AND [CodUnitate]=@codUnit
and dbo.IsContInclus(Cont,a.ContDebit,Terminal)=1


IF @@ERROR != 0 
	GOTO EROARE

--UPDATE [Conturi] SET 
--RulajCredit = CreditInitial+(SELECT Coalesce(SUM(r.ValoareLei),0) FROM Rulaje r JOIN RulajeDoc rd ON r.IDDocument=rd.IDDocument WHERE 
--year(rd.DataDoc)=@pAn and  dbo.IsContInclus(Cont ,r.ContCredit,Terminal)=1 AND r.[CodUnitate]=@codUnit)
--WHERE cont<>'N/A' AND [CodUnitate]=@codUnit

UPDATE [Conturi] SET 
RulajCredit = a.RulajCurent FROM
(
 SELECT coalesce(SUM(r.ValoareLei),0) RulajCurent,r.ContCredit  FROM Rulaje r JOIN RulajeDoc rd ON r.IDDocument=rd.IDDocument WHERE
 year(rd.DataDoc)=@pAn  AND r.[CodUnitate]=@CodUnit
 group by r.ContCredit
) a
WHERE cont<>'N/A' AND [CodUnitate]=@codUnit
and dbo.IsContInclus(Cont,a.ContCredit,Terminal)=1



IF @@ERROR != 0 
GOTO EROARE

PRINT('calculeaza soldurile finale pe terminale')
--calculeaza soldurile finale pe terminale
UPDATE [Conturi] SET [DebitPrecedent] =[DebitInitial] - [CreditInitial] +  [RulajDebit] - [RulajCredit], [CreditPrecedent]=0 WHERE (Tip='A' OR (Tip='B' and ([DebitInitial] - [CreditInitial] +[RulajDebit] - [RulajCredit]>0)))  AND Cont<>'N/A' AND [CodUnitate]=@codUnit
IF @@ERROR != 0 GOTO EROARE
UPDATE [Conturi] SET [CreditPrecedent] = [CreditInitial]  - [DebitInitial] + [RulajCredit] - [RulajDebit],[DebitPrecedent]=0 WHERE (Tip='P' OR (Tip='B' and ([DebitInitial] - [CreditInitial] + [RulajDebit] - [RulajCredit]<0)))  AND Cont<>'N/A' AND [CodUnitate]=@codUnit
IF @@ERROR != 0 GOTO EROARE
--modifica soldurile initiale
UPDATE [Conturi] SET [DebitInitial] = [DebitPrecedent],[CreditInitial] = [CreditPrecedent] WHERE Cont<>'N/A' AND [CodUnitate]=@codUnit
IF @@ERROR != 0 GOTO EROARE

-- nu trebuie sterse conturile in afara bilantului
--UPDATE [Conturi] SET [DebitInitial] = 0,[CreditInitial] = 0 WHERE Categorie<>0 AND [CodUnitate]=@CodUnit
--IF @@ERROR != 0 GOTO EROARE

UPDATE [Conturi] SET [DebitPrecedent] = 0, [CreditPrecedent]=0, [RulajDebit]=0, [RulajCredit]=0 WHERE [CodUnitate]=@codUnit
IF @@ERROR != 0 GOTO EROARE

PRINT('JurnaleSolduriInitiale')
--JurnaleSolduriInitiale---------------------------------------
DELETE FROM [arhJurnaleSolduriInitiale] WHERE [CodUnitate]=@CodUnit AND An=@pAn
IF @@ERROR != 0 
	GOTO EROARE

INSERT INTO [arhJurnaleSolduriInitiale] SELECT *,@pAn FROM [JurnaleSolduriInitiale]  WHERE [CodUnitate]=@CodUnit
IF @@ERROR != 0 
	GOTO EROARE

PRINT('Jurnale')
DELETE FROM [arhJurnale] WHERE [CodUnitate]=@CodUnit AND An=@pAn
IF @@ERROR != 0 
	GOTO EROARE

INSERT INTO [arhJurnale] SELECT *,@pAn FROM [Jurnale]  WHERE [CodUnitate]=@CodUnit
IF @@ERROR != 0 
	GOTO EROARE


PRINT('Conturi speciale')
DELETE FROM [arhConturiSpeciale] WHERE [CodUnitate]=@CodUnit AND An=@pAn
IF @@ERROR != 0 
	GOTO EROARE

INSERT INTO [arhConturiSpeciale] SELECT *,@pAn FROM [ConturiSpeciale] WHERE [CodUnitate]=@CodUnit
IF @@ERROR != 0 
	GOTO EROARE


PRINT('Banci')
DELETE FROM [arhBanci] WHERE An=@pAn
IF @@ERROR != 0 
	GOTO EROARE

INSERT INTO [arhBanci] SELECT *,@pAn FROM [Banci]
IF @@ERROR != 0 
	GOTO EROARE


PRINT('Parteneri')
DELETE FROM [arhParteneri] WHERE [CodUnitate]=@CodUnit AND An=@pAn
IF @@ERROR != 0 
	GOTO EROARE

INSERT INTO [arhParteneri] SELECT *,@pAn FROM [Parteneri]  WHERE [CodUnitate]=@CodUnit
IF @@ERROR != 0 
	GOTO EROARE

PRINT('Ordonantari')

DELETE FROM [arhOrdonantari]  WHERE [CodUnitate]=@CodUnit
IF @@ERROR != 0 GOTO EROARE

INSERT INTO [arhOrdonantari] SELECT *,@pAn FROM [Ordonantari] WHERE YEAR(DataDoc)= @pAn AND [CodUnitate]=@CodUnit
IF @@ERROR != 0 GOTO EROARE

DELETE FROM [Ordonantari] WHERE YEAR(DataDoc)=@pAn AND [CodUnitate]= @CodUnit
IF @@ERROR != 0 GOTO EROARE


PRINT('RezultatPatrimonial')
--RezultatPatrimonial---------------------------------------
DELETE FROM [arhRezultatPatrimonial] WHERE [CodUnitate]=@CodUnit AND An=@pAn
IF @@ERROR != 0 
	GOTO EROARE

INSERT INTO [arhRezultatPatrimonial] SELECT *,@pAn FROM [RezultatPatrimonial] WHERE [CodUnitate]=@CodUnit
PRINT(@Comanda)
IF @@ERROR != 0 
	GOTO EROARE

PRINT('RulajeDoc')
--RulajeDoc---------------------------------------

INSERT INTO [arhRulajeDoc] SELECT 
	   [IDDocument]
      ,[ValoareLei]
      ,[ValoareValuta]
      ,[NumarDoc]
      ,[DataDoc]
      ,[NumarNota]
      ,[TipFormula]
      ,[IDJurnal]
      ,[Observatii]
      ,[xUser]
      ,[xHost]
      ,[xDate]
      ,[CodUnitate]
      ,@pAn
      ,[IDSursaFinantare]
      ,[IDSectorBugetar]
	  FROM [RulajeDoc] WHERE YEAR(DataDoc)=@pAn AND [CodUnitate]=@CodUnit
IF @@ERROR != 0 
	GOTO EROARE

PRINT('Rulaje')
--Rulaje---------------------------------------

INSERT INTO [arhRulaje] SELECT a.[CodFormula]
      ,a.[ContDebit]
      ,a.[ContCredit]
      ,a.[CodValuta]
      ,a.[IDDocument]
      ,a.[ValoareValuta]
      ,a.[Curs]
      ,a.[ValoareLei]
      ,a.[DataDocJust]
      ,a.[TipDocJust]
      ,a.[NumarDocJust]
      ,a.[ExplicatiiDocJust]
      ,a.[NrCrt]
      ,a.[CodUnitate]
	  ,@pAn FROM [Rulaje] a JOIN [arhRulajeDoc] b ON a.IDDocument = b.IDDocument AND b.[CodUnitate]=@CodUnit
IF @@ERROR != 0 
	GOTO EROARE


DELETE FROM [RulajeDoc] WHERE YEAR(DataDoc)=@pAn AND [CodUnitate]=@CodUnit 
IF @@ERROR != 0 
	GOTO EROARE

PRINT('VenituriSiCheltuieli')
--VenituriSiCheltuieli---------------------------------------
DELETE FROM [arhVenituriSiCheltuieli] WHERE [CodUnitate]=@CodUnit AND An=@pAn
IF @@ERROR != 0 
	GOTO EROARE	

INSERT INTO [arhVenituriSiCheltuieli] SELECT *,@pAn FROM [VenituriSiCheltuieli] WHERE [CodUnitate]=@CodUnit
IF @@ERROR != 0 
	GOTO EROARE	

IF EXISTS(SELECT * FROM [Arhive] WHERE [Anul]=@pAn AND [CodUnitate]=@codUnit)
BEGIN
	UPDATE [Arhive] SET [Balcon]=1 WHERE [CodUnitate]=@CodUnit AND [Anul]=@pAn
END
ELSE
BEGIN
	INSERT INTO [Arhive] ([Denumire],[CodUnitate],[Anul],[Balcon])  VALUES (@strAn, @CodUnit, @pAn,1)
END
IF @@ERROR != 0 
	GOTO EROARE	

UPDATE [BalConSetup] SET [AnCurent] = [AnCurent] + 1 WHERE [CodUnitate] =@CodUnit 
IF @@ERROR != 0 
	GOTO EROARE	

COMMIT

GOTO Gata
EROARE:
	ROLLBACK
	RAISERROR ('Eroare la inchiderea anului!',16,-1)
	GOTO Gata
EROARE1:
	RAISERROR ('!!',16,-1)	
	GOTO Gata
Gata:

END